package br.com.ffm.praticas.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.List;

import br.com.ffm.praticas.bean.*;
import br.com.ffm.praticas.connection.ConnectionDB;
import br.com.ffm.praticas.constants.Constant;

public class SellDAO {
	
	/**
	 * Method that returns all the Sells
	 * @return List<Sell>
	 * @throws SQLException
	 */
	public List<Sell> getSells() throws SQLException {
		String query = "SELECT * FROM sell";
		SalesItemDAO salesitemDAO = new SalesItemDAO();
		EmployerDAO employerDAO = new EmployerDAO();
		SingleCostumerDAO singleCostumerDAO = new SingleCostumerDAO();
		LegalCostumerDAO  legalCostumerDao = new LegalCostumerDAO();
		List<Sell> list = new LinkedList<Sell>();
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		while (result.next()) {
			Sell sell = new Sell();
			sell.setId(result.getInt("id"));
			
			SingleCostumer singleCostumer = singleCostumerDAO.getSingleCostumer(result.getInt("costumer_id"));
			LegalCostumer legalCostumer = legalCostumerDao.getLegalCostumer(result.getInt("costumer_id"));
			
			/* DEFINE THE PERSON THAT PERTENCES TO SELL*/
			if (singleCostumer != null)
				sell.setCostumer(singleCostumer);
			else if (legalCostumer != null)
				sell.setCostumer(legalCostumer);
			sell.setEmployer(employerDAO.getEmployer(result.getInt("employer_id")));
			sell.setSellProducts(salesitemDAO.getProducts(result.getInt("id")));
			sell.setTotalSellValue(calculeSellValue(sell.getSellProducts()));
			list.add(sell);
		}
		ConnectionDB.getInstance().close();
		return list;
	}
	
	/**
	 * Method that returns a specific Sell for your id
	 * @param id Sell IDentifier
	 * @return Sell
	 * @throws SQLException
	 */
	public Sell getSell(int id) throws SQLException {
		String query = "SELECT * FROM sell WHERE (id = " + id + " )";
		SalesItemDAO salesitemDAO = new SalesItemDAO();
		EmployerDAO employerDAO = new EmployerDAO();
		SingleCostumerDAO singleCostumerDAO = new SingleCostumerDAO();
		LegalCostumerDAO  legalCostumerDao = new LegalCostumerDAO();
		Sell sell;
		ResultSet result = ConnectionDB.getInstance().executeReader(query);
		if (result.next()) {
			sell = new Sell();
			sell.setId(result.getInt("id"));
			
			SingleCostumer singleCostumer = singleCostumerDAO.getSingleCostumer(result.getInt("costumer_id"));
			LegalCostumer legalCostumer = legalCostumerDao.getLegalCostumer(result.getInt("costumer_id"));
			
			/* DEFINE THE PERSON THAT PERTENCES TO SELL*/
			if (singleCostumer != null)
				sell.setCostumer(singleCostumer);
			else if (legalCostumer != null)
				sell.setCostumer(legalCostumer);
			sell.setEmployer(employerDAO.getEmployer(result.getInt("employer_id")));
			sell.setSellProducts(salesitemDAO.getProducts(result.getInt("id")));
			sell.setTotalSellValue(calculeSellValue(sell.getSellProducts()));
			ConnectionDB.getInstance().close();
			return sell;
		}
		ConnectionDB.getInstance().close();
		return null;
	}
	
	/**
	 * Method that inserts a Sell
	 * @param sell Sell that will be inserted
	 * @return Sell Id
	 * @throws SQLException
	 */
	public int insert(Sell sell) throws SQLException {
		String query = "INSERT INTO sell (employer_id, costumer_id, sell_date, total_value) VALUES (";
		SalesItemDAO salesItemDAO = new SalesItemDAO();
		query += sell.getEmployer().getId() + ", ";
		query += sell.getCostumer().getId() + ", ";
		query += "'" + getDate(new GregorianCalendar()) + "', ";
		query += calculeSellValue(sell.getSellProducts()) + ")";
		ConnectionDB.getInstance().executeCommand(query);
		
		query = "SELECT MAX(id) FROM sell";
		int sellId = ConnectionDB.getInstance().executeScalar(query, Constant.SCALAR_COMMAND_MAX, "id");
		
		for (Product product : sell.getSellProducts())
			salesItemDAO.insert(sellId, product.getId());
		
		return sellId;
	}
	
	/**
	 * Method that deletes a Sell
	 * @param sell Sell that will be deleted
	 * @return true it will have been deleted or false if it will not be deleted
	 */
	public boolean delete(Sell sell) {
		String query = "DELETE FROM sell WHERE (id = " + sell.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			return true;
		} catch (SQLException sqlExcp) {
			return false;
		}
	}
	
	/**
	 * Method that updates a Sell
	 * @param sell Sell that will be updated
	 * @return true it will have been updates or false if it will not be updates
	 */
	public boolean update(Sell sell) {
		SalesItemDAO salesItemDAO = new SalesItemDAO();
		String query = "UPDATE sell SET ";
		query += "employer_id = " + sell.getEmployer().getId() + ", ";
		query += "costumer_id = " + sell.getCostumer().getId() + ", ";
		query += "sell_date = '" + getDate(sell.getSellDate()) + "', ";
		query += "total_value = " + calculeSellValue(sell.getSellProducts()) + " ";
		query += "WHERE (id = " + sell.getId() + " )";
		try {
			ConnectionDB.getInstance().executeCommand(query);
			
			/* DELETE ALL SALES ITENS OF THIS SELL */
			salesItemDAO.delete(sell.getId());
			
			/* INSERT ALL SALES ITENS OF THIS SELL */
			for (Product product : sell.getSellProducts())
				salesItemDAO.insert(sell.getId(), product.getId());
			
			return true;
		} catch (SQLException sqlExcp) {
			return false;
		}
	}
	
	/**
	 * Method that calculates the total value of the sell
	 * @param products Sell's Product List
	 * @return Total Value of the Sell
	 */
	private double calculeSellValue(List<Product> products) {
		double result = 0;
		for (Product p : products)
			result += p.getUnitValue();
		return round(result, 2, 0);
	}
	
	/**
	 * Method that returns a Formated Date
	 * Egg: YYYY/MM/DD -> '2009/01/13'
	 * @param date GregorianCalendar that will be formated
	 * @return Formated Date String
	 */
	private String getDate(GregorianCalendar date) {
		String formatedDate = date.get(Calendar.YEAR) + "/" + (date.get(Calendar.MONTH) + 1) + "/" + date.get(Calendar.DAY_OF_MONTH);
		return formatedDate;
	}
	
	/**
	 * Method that around a value
	 * @param value Value to round
	 * @param digits Amount of digits after the comma
	 * @param ceilOrFloor Around the value to up or to down
	 *        0 - To Up
	 *        1 - To Down
	 * @return Value around
	 */
	double round(double value, int digits, int ceilOrFloor) {  
	    double arounding = value;  
	    arounding *= (Math.pow(10, digits));  
	    if (ceilOrFloor == 0) {  
	    	arounding = Math.ceil(arounding);           
	    } else {  
	    	arounding = Math.floor(arounding);  
	    }  
	    arounding /= (Math.pow(10, digits));  
        return arounding;  
    }  

}
